In general, a database is an organized collection of data. A relational database, conceptually, can be organized as one or more tables, where a table is a two-dimensional structure with data values organized in rows and columns. A database management system (“DBMS”) mediates interactions between a database, users and applications in order to organize, create, update, capture, analyze and otherwise manage the data in the database.
An enterprise resource planning (“ERP”) system is a set of business management tools, such as a suite of integrated software applications, that a company uses to collect, store, manage and interpret data from various business activities. The business activities can include product planning, cost tracking and development, manufacturing, service delivery, marketing, sales, inventory management, shipping and payment tracking. In many cases, an ERP system contains huge amounts of data, which can be spread across relational database tables accessed and updated at different times by various departments (e.g., manufacturing, purchasing, sales, accounting, and so on). Tables may contain fields in different formats, e.g., date formats (such as MMDDYY, MMDDYYYY or DDMMYY), time formats (such as HH:MM:SS or HH:MM), formats for strings of text characters (such as leading zeros, lower case only or upper case only), number formats (such as integer or floating point), and so on. A conversion routine (also called a conversion exit) can be used to convert values between a given format used in a database table and another format used for input or presentation.
Typically, an ERP system provides status information for data in database tables. The database tables are accessed through a DBMS, which executes searches for specified data in the database tables. For example, an ERP system can be used to track the status of business resources or commitments. Several different approaches have been used to retrieve relevant data from database tables in an ERP system.
According to one approach, a search engine included in or accessed by the DBMS creates an index file for the data values in the database tables of the ERP system. FIG. 1 illustrates an example search process (100) using an index file (125) for database tables (105). The search process (100) includes a stage in which the index file (125) is constructed and a stage in which searches are performed using the index file (125).
The index file (125) is constructed from database tables (105) that may contain relevant data values for searches. The search engine reads the database tables (105), creates (110) the index file (125) from the database tables (105), and then stores (120) the index file (125) in storage or memory for use in database searches. When constructing the index file (125), the search engine “flattens” the information from the database tables (105) into a text file or similar file for the index file (125). As part of this process, the search engine converts data values in different formats to a common format for the index file (125) (e.g., a character format used by all data values, regardless of their format in the underlying database tables). The index file (125) has a simple structure and can be reviewed quickly during subsequent string search operations, since all data values in the index file (125) have the same format. From time to time, the search engine decides (130) whether to update the index file (125). The search engine can update the index file every 10 minutes, every hour, etc.
When a user enters a search string for a database search, the search engine is called. The search engine receives (140) the search string. If the search string includes multiple search values, the search engine decides whether search values should be combined with an AND condition or an OR condition for the search. The search engine searches (150) (reads) the index file (125), attempting to find the search string in the index file (125) using string search operations. String search operations on the index file (125) tend to be fast and efficient, even when multiple search values are combined with an AND condition or an OR condition. The search engine (or another module) interprets (160) the results (e.g., sorting results, prioritizing results) and returns (170) the results to the requester for presentation.
While searching the index file (125) is fast, the index file (125) becomes out-of-date when the data values in the underlying database tables (105) change. Even if the index file (125) is constructed periodically (e.g., every 10 minutes, every hour), real-time status information might not be returned in search results, since the index file (125) does not reflect the actual, current status of the data in the underlying database tables (105). The results returned by the search engine can list a “hit” (according to the index file (125)) that no longer exists in the database tables. An ERP system can include several hundred thousand database tables. Constructing the index file (125) can be time-consuming, so re-compiling the index file (125) on demand (or every second, every few seconds, etc.) is not practical.
Even if the index file (125) is up-to-date, information may be lost when the index file (125) is constructed due to format conversion operations. For example, suppose a date in a DDMMYY format (e.g., 290300) for a record is converted to a character format for the index file (125). If a search value is the same date in a different format (e.g., 03292000, 032900 or Mar. 29, 2000), a record having that date may be missed. Conversion of data values to a common character format for the index file (125) can result in loss of information about the meaning of those data values. In particular, when a customer has created a database table with a custom structure, the index file (125) may poorly represent the data values in the table.
Finally, aside from problems stemming from the index file (125) being incomplete or out-of-date, when a match is found in the index file (125), the “hit” returned (170) by the search engine lacks context. Typically, the search engine indicates the database table that includes the match, but does not provide context or details about the location of the match within the database table.
According to another approach, for a given database table, a search engine uses a data browser associated with that table. The data browser can be provided, for example, by a vendor that provides or manages the database table. The data browser accounts for different formats of fields of the database table. A user enters a search string for a field of the database table, and the associated data browser searches the field of the database table for the search string, performing any necessary format conversions. If the user does not know the database table to search or field of the database table, however, the user is unable to execute the search.
In this approach, the structure of the search area is not unified. Different data browsers are used for different database tables (which often have different fields, different formats, etc.). Searches with multiple search values, in fields of different database tables or otherwise, are not supported. For example, a given data browser cannot search for multiple search values in different database tables.